<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH w_NAVIGATION AS
(SELECT T.*,
row_number() over(order by T_USER_GROUP.ORDER_NUM, T_USER_GROUP.code, T.ORDER_NUM , t.CODE) rn ,
T_USER_GROUP.code AS parent_Code ,
T_USER_GROUP.name AS parent_Name
FROM C1_USER_GROUP T
LEFT JOIN C1_USER_GROUP T_USER_GROUP
ON ((sysdate BETWEEN T_USER_GROUP.BEGIN_DATE AND T_USER_GROUP.END_DATE)
AND T_USER_GROUP.id = t.parent_id)
WHERE (NOW() BETWEEN IFNULL(T.BEGIN_DATE, NOW()) AND IFNULL(T.END_DATE, NOW()) )
<#if StringUtils.isNotBlank(typeId)>
AND T.type_id                              = :typeId
</#if>
<#if StringUtils.isNotBlank(parentCode)>
AND (instr(','||T_USER_GROUP.code||',', ','||:parentCode||',') > 0 OR instr(','||T_USER_GROUP.name||',', ','||:parentCode||',') > 0)
</#if>
<#if StringUtils.isNotBlank(code)>
AND (instr(','||T.code||',', ','||:code||',') > 0 OR instr(','||T.name||',', ','||:code||',') > 0)
</#if>
)
SELECT T.*
, t_DICTIONARY.code as typeCode
, t_DICTIONARY.name as typeName
FROM w_NAVIGATION T
left join C1_DICTIONARY t_DICTIONARY on (t_DICTIONARY.id = t.type_id)
WHERE t.rn BETWEEN <#if (begin_row_num??)>:begin_row_num<#else>1</#if> AND <#if (end_row_num??)>:end_row_num<#else>20</#if>
order by t.rn
